﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_HrmRequest
    {
        public D_HrmRequest()
        {

        }
        public DataSet GetRequestAbsences(Nullable<int> Sequence, Nullable<int> EmpSequence, string SearchText)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@SearchText", SearchText);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT send.*"
                        + ", a.ABSENCE_SEQ, a.ABSENCE_TYPE_SEQ, a.EMP_SEQ, a.START_DATE, a.START_TIME"
                        + ", a.END_DATE, a.END_TIME, a.COUNT_DATE, a.CONTACT_ADDRESS"
                        + ", a.APPROVED_STATUS, a.APPROVED_BY, a.APPROVED_POSITION, a.APPROVED_DATE"
                        + ", a.REMARKS AS ABSENCE_REASON, a.CREATED_WHEN"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                        + ", p.POS_NAME, j.JOB_SITE_NAME, at.ABSENCE_TYPE_NAME"
                        + ", CONCAT(anp.PREFIX_NAME, ap.FIRST_NAME, '  ', ap.LAST_NAME) AS APPROVED_NAME"
                        + ", CASE WHEN a.APPROVED_STATUS = '0' THEN 'ไม่เห็นควรให้ลา'"
                            + " ELSE CASE WHEN a.APPROVED_STATUS = '1' THEN 'เห็นควรให้ลา'"
                                + " ELSE CASE WHEN a.APPROVED_STATUS = '2' THEN 'ส่งให้พิจารณาต่อ'"
                                    + " ELSE 'รอการพิจารณา'"
                                    + " END"
                                + " END"
                            + " END AS APPROVED_STATUS_NAME"
                        + ", coalesce(re.REPLY_TYPE, '0') AS REPLY_TYPE, re.REMARKS AS APPROVED_REASON"
                    + " FROM MESSAGE_SEND send"
                    + " LEFT JOIN MESSAGE_REPLY re ON send.SEND_SEQ = re.FROM_SEND_SEQ"
                    + " LEFT JOIN HRM_PS_ABSENCES a ON send.REF_SEQ = a.ABSENCE_SEQ"
                    + " LEFT JOIN HRM_PS_ABSENCE_TYPES at ON a.ABSENCE_TYPE_SEQ = at.ABSENCE_TYPE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON send.FROM_EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES ap ON a.APPROVED_BY = ap.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES anp ON ap.NAME_PREFIX_SEQ = anp.NAME_PREFIX_SEQ"
                    + " WHERE send.RECORD_STATUS <> @DeleteRecord"
                    + " AND a.RECORD_STATUS <> @DeleteRecord"
                    + (EmpSequence.HasValue ? " AND send.FROM_EMP_SEQ = @EmpSequence" : "")
                    + (Sequence.HasValue ? " AND a.ABSENCE_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public int GetAbsenceSequence()
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                string sql;
                sql = " SELECT scope_identity() as last_seq "
                    + " FROM HRM_PS_ABSENCES"
                    + " WHERE RECORD_STATUS = @ActiveRecord";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["last_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet GetAbsenceTypes(Nullable<int> Sequence, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@SearchText", SearchText);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT ABSENCE_TYPE_SEQ, ABSENCE_TYPE_NAME, DAY_COUNT_ADVANCE"
                        + ", INCLUDE_NWD, REMARKS, RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM HRM_PS_ABSENCE_TYPES"
                     + " WHERE RECORD_STATUS = @ActiveRecord"
                     + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE ABSENCE_TYPE_NAME LIKE CONCAT('%', @SearchText , '%') "
                        + " END"
                     + (Sequence.HasValue ? " AND ABSENCE_TYPE_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void InsertAbsence(M_HrmAbsences Info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@AbsenceTypeSeq", Info.AbsenceType);
                dbManager.AddParameters(1, "@EmpSeq", Info.Employee);
                dbManager.AddParameters(2, "@StartTime", Info.StartTime);
                dbManager.AddParameters(3, "@EndTime", Info.EndTime);
                dbManager.AddParameters(4, "@StartDate", Info.StartDate);
                dbManager.AddParameters(5, "@EndDate", Info.EndDate);
                dbManager.AddParameters(6, "@CountDay", Info.CountDate);
                dbManager.AddParameters(7, "@ContactAddress", Info.ContactAddress);
                dbManager.AddParameters(8, "@AppStatus", Info.Status);
                dbManager.AddParameters(9, "@AppBy", Info.ApprovedBy);
                dbManager.AddParameters(10, "@AppPosition", Info.ApprovedPosition);
                dbManager.AddParameters(11, "@AppDate", Info.ApprovedDate);
                dbManager.AddParameters(12, "@SendSequence", Info.SendSequence);
                dbManager.AddParameters(13, "@Remarks", Info.Remarks);
                dbManager.AddParameters(14, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(16, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_ABSENCES(ABSENCE_TYPE_SEQ, EMP_SEQ, START_TIME"
                        + ", END_TIME, START_DATE, END_DATE, COUNT_DATE, CONTACT_ADDRESS"
                        + ", APPROVED_STATUS, APPROVED_BY, APPROVED_POSITION, APPROVED_DATE"
                        + ", SEND_SEQ, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@AbsenceTypeSeq, @EmpSeq, @StartTime, @EndTime, @StartDate, @EndDate"
                        + ", @CountDay, @ContactAddress, @AppStatus, @AppBy, @AppPosition, @AppDate"
                        + ", @SendSequence" //", (select scope_identity() as last_seq from message_send)"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();

                //strSQL = "UPDATE MESSAGE_SEND"
                //    + " SET REF_SEQ = (select scope_identity() as last_seq from hrm_ps_absences)"
                //    + " WHERE SEND_SEQ = (select)";
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                //dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void UpdateAbsence(M_HrmAbsences Info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@AbsenceTypeSeq", Info.AbsenceType);
                dbManager.AddParameters(2, "@EmpSeq", Info.Employee);
                dbManager.AddParameters(3, "@StartTime", Info.StartTime);
                dbManager.AddParameters(4, "@EndTime", Info.EndTime);
                dbManager.AddParameters(5, "@StartDate", Info.StartDate);
                dbManager.AddParameters(6, "@EndDate", Info.EndDate);
                dbManager.AddParameters(7, "@CountDay", Info.CountDate);
                dbManager.AddParameters(8, "@ContactAddress", Info.ContactAddress);
                dbManager.AddParameters(9, "@AppStatus", Info.Status);
                dbManager.AddParameters(10, "@AppBy", Info.ApprovedBy);
                dbManager.AddParameters(11, "@AppPosition", Info.ApprovedPosition);
                dbManager.AddParameters(12, "@AppDate", Info.ApprovedDate);
                dbManager.AddParameters(13, "@Remarks", Info.Remarks);
                dbManager.AddParameters(14, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(15, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCES"
                           + " SET ABSENCE_TYPE_SEQ = @AbsenceTypeSeq"
                                + ", EMP_SEQ = @EmpSeq"
                                + ", START_DATE = @StartDate"
                                + ", START_TIME = @StartTime"
                                + ", END_DATE = @EndDate"
                                + ", END_TIME = @EndTime"
                                + ", COUNT_DATE = @CountDay"
                                + ", CONTACT_ADDRESS = @ContactAddress"
                                + ", APPROVED_STATUS = @AppStatus"
                                + ", APPROVED_BY = @AppBy"
                                + ", APPROVED_POSITION = @AppPosition"
                                + ", APPROVED_DATE = @AppDate"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE ABSENCE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void UpdateAbsenceStatus(M_HrmAbsences Info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@AppStatus", Info.Status);
                dbManager.AddParameters(2, "@AppDate", Info.ApprovedDate);
                dbManager.AddParameters(3, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(4, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCES"
                           + " SET APPROVED_STATUS = @AppStatus"
                                + ", APPROVED_DATE = @AppDate"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE ABSENCE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void DeleteAbsence(int Sequence, int UpdatedBy)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE ABSENCE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        //**************************************************************
        public DataSet GetRequestChange(Nullable<int> Sequence, Nullable<int> EmpSequence, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@SearchText", SearchText);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT req.*"
                        + ", ct.CHG_TYPE_NAME, CAST('รอการพิจารณา' AS CHAR) AS CHG_STATUS_NAME"
                    + " FROM HRM_CHG_REQUESTS req"
                    + " LEFT JOIN HRM_CHG_TYPES ct ON req.CHG_TYPE_SEQ = ct.CHG_TYPE_SEQ"
                    + " WHERE req.RECORD_STATUS <> @DeleteRecord"
                    + (EmpSequence.HasValue ? " AND req.EMP_SEQ = @EmpSequence" : "")
                    + (Sequence.HasValue ? " AND req.CHG_REQ_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public DataSet GetRequestChangeDetail(string ChangeType, Nullable<int> Sequence, Nullable<int> EmpSequence, string SearchText)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@SearchText", SearchText);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT req.*"
                    + " FROM HRM_CHG_REQUESTS req"
                    + " WHERE req.RECORD_STATUS <> @DeleteRecord"
                    + (EmpSequence.HasValue ? " AND req.EMP_SEQ = @EmpSequence" : "")
                    + (Sequence.HasValue ? " AND req.CHG_REQ_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void InsertRequestChanges(int EmpSequence, int ChangeType, string Remarks, int CreatedBy)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@ChangeType", ChangeType);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@ChangeDate", DateTime.Today);
                dbManager.AddParameters(3, "@ChangeStatus", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(4, "@ChangeRefNo", Remarks);
                dbManager.AddParameters(5, "@Remarks", Remarks);
                dbManager.AddParameters(6, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(8, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_CHG_REQUESTS(CHG_REF_NO, EMP_SEQ, CHG_TYPE_SEQ, CHG_DATE, CHG_STATUS"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@ChangeRefNo, @EmpSequence, @ChangeType, @ChangeDate, @ChangeStatus"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void UpdateRequestChanges(int Sequence, int ChangeType, string Status, string Remarks, int UpdatedBy)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@ChangeType", ChangeType);
                dbManager.AddParameters(2, "@ChangeDate", DateTime.Today);
                dbManager.AddParameters(3, "@ChangeStatus", Status);
                dbManager.AddParameters(4, "@ChangeRefNo", Remarks);
                dbManager.AddParameters(5, "@Remarks", Remarks);
                dbManager.AddParameters(6, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(8, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_CHG_REQUESTS"
                        + " SET CHG_TYPE_SEQ = @ChangeType"
                        //+ ", CHG_DATE = @ChangeDate"
                        + ", CHG_STATUS = @ChangeStatus"
                        + ", REMARKS = @Remarks"
                        //+ ", RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CHG_REQ_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void DeleteRequestChanges(int Sequence, int UpdatedBy)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_CHG_REQUESTS"
                        + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CHG_REQ_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}